﻿using MvcFileUploader;
using MvcFileUploader.Models;
using SAIM.Models.DAL;
using System;
using System.Collections.Generic;
using System.Data.Entity.Validation;
using System.Linq;
using System.Text.RegularExpressions;
using System.Web;
using System.Security.Cryptography;

namespace SAIM.Models.BLL
{
    public class SocialServiceBLL : IDisposable
    {
        private SAIMEntities db;

        public SocialServiceBLL()
        {
            db = new SAIMEntities();
        }

        /// <summary>
        /// Actualiza la informacion de servicio social de un alumno
        /// Si la informacion a actualizar no existe, se crea un nuevo registro
        /// </summary>
        /// <param name="model">Modelo</param>
        /// <returns></returns>
        public bool Update(SocialServiceViewModel model)
        {
            bool success;
            StudentDAL student = db.Student.FirstOrDefault(a=>a.Id == model.StudentId);
            HealthStatusDAL healthStatus; 
            string socialServiceId;

            try
            {
                switch (model.CurrentPage)
                {
                    case 0: // Datos Personales
                        if (student != null) // Actualizacion de datos
                        {
                            student.Name = model.StudentName;
                            student.BirthDate = DateTime.Parse(model.BirthDate, new System.Globalization.CultureInfo("es-mx"));
                            student.CellPhone = model.CellPhoneNumber;
                            student.Email = model.Email;
                            student.AddressInformation.Street = model.Address;
                            student.AddressInformation.PhoneNumber = model.StudentPhoneNumber;
                            student.StudentAddress = model.Address;
                            student.StudentPhoneNumber = model.StudentPhoneNumber;
                        }
                        else // Creacion de nuevo registro
                        {
                            AddressInformationDAL address = new AddressInformationDAL()
                            {
                                Street = model.Address,
                                PhoneNumber = model.StudentPhoneNumber
                            };
                            student = new StudentDAL()
                            {
                                Id = model.StudentId,
                                Name = model.StudentName,
                                BirthDate = DateTime.Parse(model.BirthDate, new System.Globalization.CultureInfo("es-mx")),
                                CellPhone = model.CellPhoneNumber,
                                Email = model.Email,
                                StudentAddress = model.Address,
                                StudentPhoneNumber = address.PhoneNumber,
                                StayModality="",
                                StayModalityGrade="",
                                StayModalityType="",
                            };
                            db.Student.Add(student);
                        }
                        socialServiceId = UpdateSocialServiceInformation(model);
                        break;
                    case 1: // Datos Academicos
                        // Facultad y carrera
                        socialServiceId = UpdateSocialServiceInformation(model);
                        SchoolDAL school = db.School.FirstOrDefault(a=>a.AcademicEntity == model.School && a.Career == model.Career);
                        if(school == null)
                        {
                            school = new SchoolDAL()
                            {
                                Id = Security.Hash(model.School + '/' + model.Career),
                                AcademicEntity = model.School,
                                Career = model.Career
                               
                            };
                            db.School.Add(school);
                        }
                        // Relacion alumno carrera, se indica el semestre
                        StudentSchoolDAL studentSchool = db.StudentSchool.FirstOrDefault(a => a.StudentId == model.StudentId && a.SchoolId == school.Id);
                        if(studentSchool == null)
                        {
                            // Obtenemos todas las relaciones estudiante escuela mediante la clave única del estudiante
                            var relationShips = (from rs in db.StudentSchool where rs.StudentId == model.StudentId select rs);
                            foreach (var item in relationShips)
                            {
                                db.StudentSchool.Remove(item);
                            }
                            studentSchool = new StudentSchoolDAL()
                            {
                                SchoolId = school.Id,
                                StudentId = model.StudentId,
                                Semester = model.Semester.Value
                                
                            };
                            db.StudentSchool.Add(studentSchool);
                        }
                        else
                        {
                            studentSchool.Semester = model.Semester.Value;
                        }
                        break;
                    case 2: // Actividades y Horario
                        socialServiceId = UpdateSocialServiceInformation(model);
                        // Si ya existen horarios se eliminan para agregarlos nuevamente.
                        List<ScheduleDAL> schedules = (from e in db.Schedule  where e.SocialServiceId == socialServiceId select e).ToList();
                        // Eliminamos los horarios
                        foreach (ScheduleDAL item in schedules)
                        {
                            db.Schedule.Remove(item);
                        }
                        // Horario matutino
                        ScheduleDAL morningSchedule = new ScheduleDAL()
                        {
                            CheckIn = model.CheckInTime1,
                            //Departure = model.DepartureTime1.Value, Se omitio por que solo se requiere un Horario 
                            SocialServiceId = socialServiceId
                        };
                        db.Schedule.Add(morningSchedule);
                        // Horario vespertino
                        if ((model.CheckInTime2 != null) && (model.DepartureTime2 != null))
                        {
                            ScheduleDAL afternoonSchedule = new ScheduleDAL()
                            {
                                CheckIn = model.CheckInTime2,
                                Departure = model.DepartureTime2.Value,
                                SocialServiceId = socialServiceId
                            };
                            db.Schedule.Add(afternoonSchedule);
                        }
                        break;
                    case 3: // Carta de Asignacion

                        student.StayModality = model.StayModality;
                        student.StayModalityGrade = model.StayModalityGrade;
                        student.StayModalityType = model.StayModalityType;
                        socialServiceId = UpdateSocialServiceInformation(model);
                        
                        break;
                    case 4: // Seguro Medico y Estado de Salud 1
                        socialServiceId = UpdateSocialServiceInformation(model);
                        healthStatus = db.HealthStatus.FirstOrDefault(a => a.StudentId == model.StudentId);
                        if (healthStatus == null)
                        {
                            healthStatus = new HealthStatusDAL()
                            {
                                StudentId = model.StudentId,
                                InsuranceType = model.InsuranceType,
                                InsuranceNumber = model.InsuranceNumber,
                                BloodType = model.BloodType,
                                Asthma = model.Asthma,
                                Diabetes = model.Diabetes,
                                Headache = model.Headache,
                                Epilepsy = model.Epilepsy,
                                DigestiveProblems = model.DigestiveProblems,
                                CardiacAfections = model.CardiacAfections
                            };
                            db.HealthStatus.Add(healthStatus);
                        }
                        else
                        {
                            healthStatus.StudentId = model.StudentId;
                            healthStatus.InsuranceType = model.InsuranceType;
                            healthStatus.InsuranceNumber = model.InsuranceNumber;
                            healthStatus.BloodType = model.BloodType;
                            healthStatus.Asthma = model.Asthma;
                            healthStatus.Diabetes = model.Diabetes;
                            healthStatus.Headache = model.Headache;
                            healthStatus.Epilepsy = model.Epilepsy;
                            healthStatus.DigestiveProblems = model.DigestiveProblems;
                            healthStatus.CardiacAfections = model.CardiacAfections;
                        }
                        break;
                    case 5: // Seguro Medico y Estado de Salud 2
                        socialServiceId = UpdateSocialServiceInformation(model);
                        healthStatus = db.HealthStatus.FirstOrDefault(a => a.StudentId == model.StudentId);
                        if (healthStatus == null)
                        {
                            healthStatus = new HealthStatusDAL()
                            {
                                StudentId = model.StudentId,
                                InsuranceType = model.InsuranceType,
                                InsuranceNumber = model.InsuranceNumber,
                                BloodType = model.BloodType,
                                Asthma = model.Asthma,
                                Diabetes = model.Diabetes,
                                Headache = model.Headache,
                                Epilepsy = model.Epilepsy,
                                DigestiveProblems = model.DigestiveProblems,
                                CardiacAfections = model.CardiacAfections,
                                VisionAffections = model.VisionAffections,
                                SkinAffections = model.SkinAffections,
                                RespiratoryProblems = model.RespiratoryProblems,
                                Disability = model.Disability,
                                Allergies = model.Allergies,
                                Hypertension = model.Hypertension,
                                Specials = model.Specials,
                                LastTetanusVaccine = model.LastTetanusVaccine,
                                SpecialMedication = model.SpecialMedicationText
                            };
                            db.HealthStatus.Add(healthStatus);
                        }
                        else
                        {
                            healthStatus.VisionAffections = model.VisionAffections;
                            healthStatus.SkinAffections = model.SkinAffections;
                            healthStatus.RespiratoryProblems = model.RespiratoryProblems;
                            healthStatus.Disability = model.Disability;
                            healthStatus.Allergies = model.Allergies;
                            healthStatus.Hypertension = model.Hypertension;
                            healthStatus.Specials = model.Specials;
                            healthStatus.LastTetanusVaccine = model.LastTetanusVaccine;
                            healthStatus.SpecialMedication = model.SpecialMedicationText;
                        }
                        break;
                    case 6: // Contacto en Caso de Emergencia
                        socialServiceId = UpdateSocialServiceInformation(model);
                        contactDAL primaryContact = db.contact.FirstOrDefault(a => a.StudentId == model.StudentId && a.Name == model.PrimaryContactName);
                        AddressInformationDAL primaryContactAddress = db.AddressInformation.FirstOrDefault(a => a.Street == model.PrimaryContactAddress);
                        if(primaryContactAddress == null)
                        {
                            primaryContactAddress = new AddressInformationDAL()
                            {
                                Street = model.PrimaryContactAddress,
                                PhoneNumber = model.PrimaryContactPhone
                            };
                            db.AddressInformation.Add(primaryContactAddress);
                        }
                        if(primaryContact == null)
                        {
                            primaryContact = new contactDAL()
                            {
                                Relationship = model.PrimaryContactRelationship,
                                Name = model.PrimaryContactName,
                                StudentId = model.StudentId,
                                ContactAddress = model.PrimaryContactAddress,
                                CellPhone = model.PrimaryContactCellphone,
                                ContactPhoneNumber = model.PrimaryContactPhone
                            };
                            db.contact.Add(primaryContact);
                        }
                        else
                        {
                            primaryContact.Relationship = model.PrimaryContactRelationship;
                            primaryContact.Name = model.PrimaryContactName;
                            primaryContact.StudentId = model.StudentId;
                            primaryContact.ContactAddress = model.PrimaryContactAddress;
                            primaryContact.CellPhone = model.PrimaryContactCellphone;
                            primaryContact.ContactPhoneNumber = model.PrimaryContactPhone;
                        }
                        //El segundo contacto no es obligatio 
                        if (model.SecundaryContactName != null && model.SecundaryContactRelationship != null)
                        {
                            // Contacto secundario (Opcional)
                            contactDAL secundaryContact = db.contact.FirstOrDefault(a => a.StudentId == model.StudentId && a.Name == model.SecundaryContactName);
                            AddressInformationDAL secundaryContactAddress = db.AddressInformation.FirstOrDefault(a => a.Street == model.SecundaryContactAddress);
                            if (secundaryContactAddress == null)
                            {
                                secundaryContactAddress = new AddressInformationDAL()
                                {
                                    Street = model.SecundaryContactAddress,
                                    PhoneNumber = model.SecundaryContactPhone
                                };
                                db.AddressInformation.Add(secundaryContactAddress);
                            }
                            if (secundaryContact == null)
                            {
                                secundaryContact = new contactDAL()
                                {
                                    Relationship = model.SecundaryContactRelationship,
                                    Name = model.SecundaryContactName,
                                    StudentId = model.StudentId,
                                    ContactAddress = model.SecundaryContactAddress,
                                    CellPhone = model.SecundaryContactCellphone,
                                    ContactPhoneNumber = model.SecundaryContactPhone
                                };
                                db.contact.Add(secundaryContact);
                            }
                            else
                            {
                                secundaryContact.Relationship = model.SecundaryContactRelationship;
                                secundaryContact.Name = model.SecundaryContactName;
                                secundaryContact.StudentId = model.StudentId;
                                secundaryContact.ContactAddress = model.SecundaryContactAddress;
                                secundaryContact.CellPhone = model.SecundaryContactCellphone;
                                secundaryContact.ContactPhoneNumber = model.SecundaryContactPhone;
                            }
                        }
                        break;
                }
                db.SaveChanges();
                success = true;
            }
            catch(Exception )
            {
                success = false;
            }
            return success;
        }
        
        private string UpdateSocialServiceInformation(SocialServiceViewModel model)
        {
            string id;
            SocialServiceInformationDAL socialService;
            
            try 
            {
                // Informacion de servicio social
                socialService = db.SocialServiceInformation.FirstOrDefault(a => a.StudentId == model.StudentId);
                if (socialService == null)
                {
                    socialService = new SocialServiceInformationDAL()
                    {
                        Id = Security.Hash("SocialService/" + model.StudentId),
                        StudentId = model.StudentId,
                        Responsible = model.ResponsibleSocialService ?? "",
                        ProyectResponsible = model.ResponsiblePerson ?? "",
                        ProjectName = model.Project,
                        Laboratories = model.ActivitiesLaboratories,
                        Activities = model.ActivitiesToDo ?? "",
                        Step = model.CurrentPage,
                        
                                                
                        
                    };
                    if (model.InitialDate != null && model.InitialDate != string.Empty)
                    {
                        socialService.InitialDate = DateTime.Parse(model.InitialDate, new System.Globalization.CultureInfo("es-mx"));
                    }
                    if (model.EndDate != null && model.EndDate != string.Empty)
                    {
                        socialService.EndDate = DateTime.Parse(model.EndDate, new System.Globalization.CultureInfo("es-mx"));
                    }
                    db.SocialServiceInformation.Add(socialService);
                }
                else
                {
                    socialService.Student.StayModality = model.StayModality ?? "";
                    socialService.Student.StayModalityGrade = model.StayModalityGrade ?? "";
                    socialService.Student.StayModalityType = model.StayModalityType ?? "";
                    socialService.Responsible = model.ResponsibleSocialService ?? "";
                    socialService.ProyectResponsible = model.ResponsiblePerson??"";
                    socialService.ProjectName = model.Project;
                    socialService.Laboratories = model.ActivitiesLaboratories;
                    socialService.Activities = model.ActivitiesToDo ?? "";
                    socialService.Student.StudentPhoneNumber = model.StudentPhoneNumber;
                    if (model.InitialDate != null && model.InitialDate != string.Empty)
                    {
                        socialService.InitialDate = DateTime.Parse(model.InitialDate, new System.Globalization.CultureInfo("es-mx"));
                    }
                    if (model.EndDate != null && model.EndDate != string.Empty)
                    {
                        socialService.EndDate = DateTime.Parse(model.EndDate, new System.Globalization.CultureInfo("es-mx"));
                    }
                    if (model.CurrentPage > socialService.Step)
                    {
                        socialService.Step = model.CurrentPage;
                    }
                }

                db.SaveChanges();
                id = socialService.Id;
            }
            catch { id = null; }
            return id;
        }

        public SocialServiceStudents GetStudents(int current, int rowCount, Dictionary<object, string> sort, string searchPhrase)
        {
            List<SocialServiceInformationDAL> items;
            SocialServiceStudents gridData; // Variable a devolver al grid

            
            // Ordenamos los elementos mediante el diccionario proveniente del BootGrid
            switch (sort.Keys.First().ToString())
            {

                case "StudentId":
                    items = (from e in db.SocialServiceInformation
                             select e).ToList();

                     items = sort.ElementAt(0).Value.ToString().Contains("asc") ?
                        items.OrderBy(a => a.StudentId).ToList() :
                        items.OrderBy(a => a.StudentId).Reverse().ToList();                    
                    break;
                case "StudentName":
                    items = (from e in db.SocialServiceInformation
                             select e).ToList();

                    items = sort.ElementAt(0).Value.ToString().Contains("asc") ?
                       items.OrderBy(a => a.Student.Name).ToList() :
                       items.OrderBy(a => a.Student.Name).Reverse().ToList();
                    break;

                case "StayModalityType":
                    items = (from e in db.SocialServiceInformation
                             select e).ToList();

                    items = sort.ElementAt(0).Value.ToString().Contains("asc") ?
                       items.OrderBy(a => a.Student.StayModalityType).ToList() :
                       items.OrderBy(a => a.Student.StayModalityType).Reverse().ToList();
                    break;
                case "InitialDate":
                    items = (from e in db.SocialServiceInformation
                             select e).ToList();

                    items = sort.ElementAt(0).Value.ToString().Contains("asc") ?
                       items.OrderBy(a => a.InitialDate).ToList() :
                       items.OrderBy(a => a.InitialDate).Reverse().ToList();
                    break;

                case "EndDate":
                    items = (from e in db.SocialServiceInformation
                             select e).ToList();

                    items = sort.ElementAt(0).Value.ToString().Contains("asc") ?
                       items.OrderBy(a => a.EndDate).ToList() :
                       items.OrderBy(a => a.EndDate).Reverse().ToList();
                    break;
                
                default:
                    items = (from e in db.SocialServiceInformation
                             select e).ToList();
                    break;
            }

            // Inicialización
            gridData = new SocialServiceStudents()
            {
                current = current,
                rowCount = rowCount,
                total = items.Count
            };
            // Llenamos los registros de usuarios
            //Validaciones para busqueda del bootgrid
            if (searchPhrase != "")//Si se esta buscando algun registro por medio de cadena
            {

                foreach (SocialServiceInformationDAL item in items)
                {
                    if (item.StudentId.ToString().ToUpper().Contains(searchPhrase.ToUpper()) || item.Student.Name.ToString().ToUpper().Contains(searchPhrase.ToUpper()) || item.Student.StayModality.ToUpper().Contains(searchPhrase.ToUpper()) || item.EndDate.ToString().ToUpper().Contains(searchPhrase.ToUpper()) || item.InitialDate.ToString().ToUpper().Contains(searchPhrase.ToUpper()))
                    {
                        gridData.rows.Add(new StudentInfo()
                        {
                            SocialServiceId = item.Id,
                            StudentId = item.StudentId,
                            StudentName = item.Student.Name,
                            InitialDate = item.InitialDate != null ? item.InitialDate.Value.ToShortDateString() : string.Empty,
                            EndDate = item.EndDate != null ? item.EndDate.Value.ToShortDateString() : string.Empty,
                            StayModality = item.Student.StayModality,
                            StayModalityGrade = item.Student.StayModalityGrade,
                            StayModalityType = item.Student.StayModalityType
                        });
                    }
                }


            }//fin if seach
            else//Rellenar los registros sin busqueda (default)
            {
                foreach (SocialServiceInformationDAL item in items)
                {
                  
                    gridData.rows.Add(new StudentInfo()
                    {
                        SocialServiceId = item.Id,
                        StudentId = item.StudentId,
                        StudentName = item.Student.Name,
                        InitialDate = item.InitialDate != null ? item.InitialDate.Value.ToShortDateString() : string.Empty,
                        EndDate = item.EndDate != null ? item.EndDate.Value.ToShortDateString() : string.Empty,
                        StayModality = item.Student.StayModality,
                        StayModalityGrade = item.Student.StayModalityGrade,
                        StayModalityType = item.Student.StayModalityType
                    });
                    
                }
            
            
            }//fin else
            //Fin validaciones


           



            return gridData;
        }

        public SocialServiceViewModel GetInformation(string id)
        {
            SocialServiceInformationDAL socialServie = (from e in db.SocialServiceInformation where e.Id == id select e).FirstOrDefault();

            SocialServiceViewModel model = new SocialServiceViewModel() 
            {
                StudentId = socialServie.StudentId,
                StudentName= socialServie.Student.Name,
                ResponsibleSocialService = socialServie.Responsible,
                ResponsiblePerson = socialServie.ProyectResponsible,
                Project = socialServie.ProjectName,
                ActivitiesLaboratories = socialServie.Laboratories,
                ActivitiesToDo = socialServie.Activities,
                InitialDate = socialServie.InitialDate != null ? socialServie.InitialDate.Value.ToShortDateString() : string.Empty,
                EndDate = socialServie.EndDate != null ? socialServie.EndDate.Value.ToShortDateString() : string.Empty,
                BirthDate = socialServie.Student.BirthDate.ToShortDateString(),
                Email = socialServie.Student.Email,
                CellPhoneNumber = socialServie.Student.CellPhone,
                //StudentPhoneNumber = socialServie.Student.AddressInformation.PhoneNumber,
                StudentPhoneNumber = socialServie.Student.StudentPhoneNumber,
                Address = socialServie.Student.StudentAddress,
                CurrentPage = socialServie.Step.Value,
                StayModality = socialServie.Student.StayModality,
                StayModalityGrade = socialServie.Student.StayModalityGrade,
                StayModalityType = socialServie.Student.StayModalityType
            };

            if(socialServie.Student.StudentSchool.Count > 0)
            {
                model.School = socialServie.Student.StudentSchool.First().School.AcademicEntity;
                model.Career = socialServie.Student.StudentSchool.First().School.Career;
                model.Semester = socialServie.Student.StudentSchool.First().Semester;
            }

            if(socialServie.Schedule.Count > 1)
            {
                // Matutino
                model.CheckInTime2 = socialServie.Schedule.ElementAt(1).CheckIn;
                model.DepartureTime2 = socialServie.Schedule.ElementAt(1).Departure;
                // Vespertino
                model.CheckInTime1 = socialServie.Schedule.ElementAt(0).CheckIn;
                model.DepartureTime1 = socialServie.Schedule.ElementAt(0).Departure;
            }
            else if (socialServie.Schedule.Count > 0)
            {
                model.CheckInTime1 = socialServie.Schedule.ElementAt(0).CheckIn;
                model.DepartureTime1 = socialServie.Schedule.ElementAt(0).Departure;
            }
            // Seguro médico y estado de salud
            if(socialServie.Student.HealthStatus != null)
            {
                // Primera parte
                model.InsuranceType = socialServie.Student.HealthStatus.InsuranceType;
                model.InsuranceNumber = socialServie.Student.HealthStatus.InsuranceNumber;
                model.BloodType = socialServie.Student.HealthStatus.BloodType;
                model.Asthma = socialServie.Student.HealthStatus.Asthma;
                model.Diabetes = socialServie.Student.HealthStatus.Diabetes;
                model.Headache = socialServie.Student.HealthStatus.Headache;
                model.Epilepsy = socialServie.Student.HealthStatus.Epilepsy;
                model.DigestiveProblems = socialServie.Student.HealthStatus.DigestiveProblems;
                model.CardiacAfections = socialServie.Student.HealthStatus.CardiacAfections;
                // Segunda parte
                model.VisionAffections = socialServie.Student.HealthStatus.VisionAffections;
                model.SkinAffections = socialServie.Student.HealthStatus.SkinAffections;
                model.RespiratoryProblems = socialServie.Student.HealthStatus.RespiratoryProblems;
                model.Disability = socialServie.Student.HealthStatus.Disability;
                model.Allergies = socialServie.Student.HealthStatus.Allergies;
                model.Hypertension = socialServie.Student.HealthStatus.Hypertension;
                model.Specials = socialServie.Student.HealthStatus.Specials;
                model.SpecialMedicationText = socialServie.Student.HealthStatus.SpecialMedication;
                if(socialServie.Student.HealthStatus.LastTetanusVaccine != null)
                {
                    model.LastTetanusVaccine = socialServie.Student.HealthStatus.LastTetanusVaccine.ToString();
                }
            }
            if (socialServie.Student.contact.Count > 0) 
            {
                model.PrimaryContactRelationship = socialServie.Student.contact.ElementAt(0).Relationship;
                model.PrimaryContactName = socialServie.Student.contact.ElementAt(0).Name;
                model.PrimaryContactAddress = socialServie.Student.contact.ElementAt(0).ContactAddress;
                //model.PrimaryContactPhone = socialServie.Student.contact.ElementAt(0).AddressInformation.PhoneNumber;
                model.PrimaryContactPhone = socialServie.Student.contact.ElementAt(0).ContactPhoneNumber;
                model.PrimaryContactCellphone = socialServie.Student.contact.ElementAt(0).CellPhone;
                
                try 
                {
                    model.SecundaryContactRelationship = socialServie.Student.contact.ElementAt(1).Relationship;
                    model.SecundaryContactName = socialServie.Student.contact.ElementAt(1).Name;
                    model.SecundaryContactAddress = socialServie.Student.contact.ElementAt(1).AddressInformation.Street;
                    //model.SecundaryContactPhone = socialServie.Student.contact.ElementAt(1).AddressInformation.PhoneNumber;
                    model.SecundaryContactPhone = socialServie.Student.contact.ElementAt(1).ContactPhoneNumber;
                    model.SecundaryContactCellphone = socialServie.Student.contact.ElementAt(1).CellPhone;
                }
                catch(Exception )
                {
                    // NOTHING TO DO. El alumno solo tiene un contacto capturado
                }
            }
            return model;
        }

        public void Dispose()
        {
            db.Dispose();
        }
        public static string PathForSaving
        {
            get
            {
                return "~/App_Data/SAIM_Uploads/";
            }
        }

        //--------------------------Codigo agregado Rafael 10/09/2017--------------------------------------------------------//
        /// <summary>
        /// Agrega a la base de datos la información de un archivo adjuntado 
        /// </summary>
        /// <param name="fileName">Nombre del archivo</param>
        /// <param name="filePath">Localización del archivo</param>
        /// <param name="fileSize">Tamaño del archivo</param>
        /// <param name="userId">ID de usuario (Dueño del archivo)</param>
        /// <returns>Verdadero (true) si se agrego correctamente, de lo contrario Falso (false)</returns>
        public ViewDataUploadFileResult AttachFileSocialService(HttpPostedFileBase fileNameSocialService, string userId, string filePath,string type)
        {
            string fileName;
            string fileNameEncript = null ;
            ViewDataUploadFileResult st = null;
            fileName = fileNameSocialService.FileName;
            //Buscar si ya existe en la base de datos ese usuario con archivos subidos
            SocialServiceAttachmentDAL attachment = (from e in db.SocialServiceAttachment where e.SocialServiceId.ToString() == userId select e).FirstOrDefault();
            //Creacion de lo que se va insertar en la tabla de la base de datos
            SocialServiceInformationDAL socialServie = (from e in db.SocialServiceInformation where e.StudentId.ToString() == userId select e).FirstOrDefault();
            
            try
            {
                switch (type)
                {
                    case "CartaDeAsignacion":
                        if (attachment != null)
                            if (attachment.AssignmentLetter != null)//Si ya existe en la base de datos solo se actualizan datos
                            {
                                //socialServie.SocialServiceAttachment.AcceptanceLetter = Security.Hash(fileName);
                                fileNameEncript = attachment.AssignmentLetter;
                                //socialServie.SocialServiceAttachment.HealthInsurance = Security.Hash(fileName);       
                            
                            }//fin if
                            else//Se inserta el campo AssigmentLetter
                            {
                                //attachment.AssignmentLetter = Security.Hash(userId + "/" + fileNameSocialService.FileName); 
                                attachment.AssignmentLetter = fileNameSocialService.FileName; 
                                //Aqui se guarda en el servidor(sql server)
                                fileName = attachment.AssignmentLetter;
                                //db.SocialServiceAttachment.Add(attachment);
                            }//fin else    
                        else//Si no existe un registro se crea uno nuevo
                            {
                                attachment = new SocialServiceAttachmentDAL()
                                {
                                    SocialServiceId = userId,
                                    //Se encripta por medio de la clave unica del estudiante y el nombre del archivo
                                    //AssignmentLetter = Security.Hash(userId + "/" + fileNameSocialService.FileName),
                                    AssignmentLetter = (fileNameSocialService.FileName),
                                    FilePath = filePath
                                
                                };
                                //Aqui se guarda en el servidor(sql server)
                                fileName = attachment.AssignmentLetter;
                                db.SocialServiceAttachment.Add(attachment);
                            }//fin else       

                        break;

                    case "CartaDeAceptacion":
                        if(attachment!=null)
                            if (attachment.AcceptanceLetter != null)//Si ya existe en la base de datos solo se actualizan datos
                            {
                                fileNameEncript = attachment.AcceptanceLetter;
                                //socialServie.SocialServiceAttachment.AssignmentLetter = Security.Hash(fileName);
                                //socialServie.SocialServiceAttachment.HealthInsurance = Security.Hash(fileName);                
                            }//fin if
                            else//Se actualiza el campo AcceptanceLetter
                            {
                                //attachment.AcceptanceLetter = Security.Hash(userId + "/" + fileNameSocialService.FileName);
                                attachment.AcceptanceLetter = fileNameSocialService.FileName;
                                //Aqui se guarda en el servidor(sql server)
                                fileName = attachment.AcceptanceLetter;
                                //db.SocialServiceAttachment.Add(attachment);
                            }//fin else
                        else//Si no existe se crea uno nuevo
                            {
                                attachment = new SocialServiceAttachmentDAL()
                                {
                                    SocialServiceId = userId,
                                    //AcceptanceLetter = Security.Hash(userId + "/" + fileNameSocialService.FileName),
                                    AcceptanceLetter = (fileNameSocialService.FileName),
                                    FilePath = filePath
                                };
                                //Aqui se guarda en el servidor(sql server)
                                fileName = attachment.AcceptanceLetter;
                                db.SocialServiceAttachment.Add(attachment);
                            }//fin else

                        break;
                    case "SeguroMedico":
                        if(attachment!=null)
                            if (attachment.HealthInsurance != null)//Si ya existe en la base de datos solo se actualizan datos
                            {
                                fileNameEncript = attachment.HealthInsurance;
                                //socialServie.SocialServiceAttachment.AssignmentLetter = Security.Hash(fileName);
                                //socialServie.SocialServiceAttachment.HealthInsurance = Security.Hash(fileName);                
                            }//fin if
                            else//Si no existe se crea uno nuevo
                            {
                                //attachment.HealthInsurance = Security.Hash(userId + "/" + fileNameSocialService.FileName);
                                attachment.HealthInsurance = fileNameSocialService.FileName;                               
                                //Aqui se guarda en el servidor(sql server)
                                fileName = attachment.HealthInsurance;
                                //db.SocialServiceAttachment.Add(attachment);
                            }//fin else
                          
                        else//Si no existe se crea uno nuevo
                            {
                                attachment = new SocialServiceAttachmentDAL()
                                {
                                    SocialServiceId = userId,
                                    //Se encripta por medio de la clave unica del estudiante y el nombre del archivo
                                    //HealthInsurance = Security.Hash(userId + "/" + fileNameSocialService.FileName),
                                    HealthInsurance = fileNameSocialService.FileName,                                    
                                    FilePath = filePath
                                
                                };
                                //Aqui se guarda en el servidor(sql server)
                                fileName = attachment.HealthInsurance;
                                db.SocialServiceAttachment.Add(attachment);
                            }//fin else   

                        break;
                    default:
                        break;
                }//Fin Switch 
               
             db.SaveChanges();
            }//fin try 
            catch (Exception e)
            {
                st = new ViewDataUploadFileResult() { error = e.Message };//Si existio error regresa un mensaje de error
                return st;
            }
            #region Alamacenamiento del archivo en el servidor---------------------------------------------------------------------
            if (fileNameEncript == null)//Si se creo por primera vez el documento
            {
                st = FileSaver.StoreFile(x =>
                {
                    x.File = fileNameSocialService;
                    x.StorageDirectory = filePath ?? PathForSaving;
                    x.UrlPrefix = PathForSaving;// Url relativa del archivo    
                    //overriding defaults
                    x.FileName = fileName;//Se encripta por el nombre del archivo y el id del alumno
                    //x.FileName = attachment.Mask;// Nombre de archivo encriptado tambien se puede guardar con el nombre encriptado
                    x.ThrowExceptions = true;//default is false, if false exception message is set in error property
                });
            }
            else//si se actualiza el documento
            {
                st = FileSaver.StoreFile(x =>
                {
                    x.File = fileNameSocialService;
                    x.StorageDirectory = filePath ?? PathForSaving;
                    x.UrlPrefix = PathForSaving;// Url relativa del archivo    
                    //overriding defaults
                    x.FileName = fileNameEncript;//Se encripta por el nombre del archivo y el id del alumno
                    //x.FileName = attachment.Mask;// Nombre de archivo encriptado tambien se puede guardar con el nombre encriptado
                    x.ThrowExceptions = true;//default is false, if false exception message is set in error property
                });
            
            }
            #endregion
            return st;
        }
        //--------------------------Fin Codigo agregado Rafael 10/09/2017--------------------------------------------------------//
        
        //Codigo para cuando se necesita descargar un documento del servicio social//
        public void GetAttachmentName(long ticketId, string typeDocument, ref string filePath, ref string fileName)
        {
            SocialServiceAttachmentDAL attachment = null;

            switch (typeDocument)
            {
                case "CartaAsignacion":
                         
                    attachment = (from e in db.SocialServiceAttachment where e.SocialServiceId.ToString() == ticketId.ToString() select e).FirstOrDefault();        
                    if (attachment != null)
                    {
                        filePath = attachment.FilePath;                        
                        fileName = attachment.AssignmentLetter;
                    }
                    else
                    {
                        filePath = fileName = string.Empty;
                    }
                    break;

                case "CartaAceptacion":
                     
                    attachment = (from e in db.SocialServiceAttachment where e.SocialServiceId.ToString() == ticketId.ToString() select e).FirstOrDefault();        
                    if (attachment != null)
                    {
                        filePath = attachment.FilePath;
                        fileName = attachment.AcceptanceLetter;
                    }
                    else
                    {
                        filePath = fileName = string.Empty;
                    }
                   
                    break;


                case "SeguroMedico":
                    
                    attachment = (from e in db.SocialServiceAttachment where e.SocialServiceId.ToString() == ticketId.ToString() select e).FirstOrDefault();        
                    if (attachment != null)
                    {
                        filePath = attachment.FilePath;
                        fileName = attachment.HealthInsurance;
                    }
                    else
                    {
                        filePath = fileName = string.Empty;
                    }                                     
                    break;

                default:
                    break;
            }
           
              
        }

    }
}